﻿<?xml version="1.0" encoding="utf-8" ?>
<AdoNetDataAccess connection="$ContactsDBConnection$" formatter="bizobj of Contact(addresses(country))">
	<Sql>
    SELECT
    c.id AS id
    ,lastName
    ,firstName
    ,middleName
    ,phone
    ,mobile
    ,email
    ,isUser
    ,c.typeId AS type_id
    ,c.companyId
    ,c.dateCreated, c.dateUpdated
    ,c.extId,c.avitarImage,c.customTitle,c.dateOfBirth,c.gender,c.location
    ,ad.id AS addressesId
    ,street1 as addressesStreet1
    ,street2 as addressesStreet2
    ,street3 as addressesStreet3
    ,city as addressesCity
    ,stateProvince as addressesStateProvince
    ,zipCode as addressesZipCode
    ,contactId as addressesContactId
    ,countries.code as countryregionCode
    ,countries.englishName as countryEnglishName
    ,latitude as addresseslatitude
    ,longitude as addresseslongitude
    ,at.id as addressestype_id
    ,at.mapImageName as addressestype_mapImageName
    ,at.currentMapImageName as addressestype_currentMapImageName
    ,at.cacheName as addressestype_cacheName
    ,a.baseTitle as addressestype_baseTitle

    FROM Contacts c
    LEFT JOIN ADDRESSES ad on ad.contactId = c.id
    LEFT join countries on countries.code  = ad.countryCode
    LEFT OUTER JOIN AddressTypes AS at on at.id = ad.typeId
    LEFT OUTER JOIN Articles AS a on a.id = at.id
    WHERE c.id=@contactId
  </Sql>
	<Sql type="SqlServer">

	</Sql>
	<Param name="contactId" type="UniqueIdentifier" />
</AdoNetDataAccess>